﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using UniversityStudentManagemnetSystem.DAO.DAL;

namespace UniversityStudentManagemnetSystem.DAO.Gateway
{
    public class StudentGateway:Gateway
    {
        Student aStudent=new Student();

        public DataTable GetAllStudents()
        {
            try
            {
               
               string selectQuery = string.Format("select * from t_StudentRegistation ");
               SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
               DataSet dataSet = new DataSet();
               sqlDataAdapter.Fill(dataSet, "StudentTable");
               DataTable myDataTable = dataSet.Tables["StudentTable"];
               return myDataTable;
            }
            catch (Exception exception)
            {
                
                throw new Exception("System Error!",exception);
            }
        }

        public DataTable GetAllStudentsFor(int selectedDepartment)
        {
            try
            {
                string selectQuery = string.Format("select * from t_StudentRegistation  where DepartmentId='"+selectedDepartment+"'");
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
                DataSet dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet, "StudentTable");
                DataTable myDataTable = dataSet.Tables["StudentTable"];
                return myDataTable;
            }
            catch (Exception exception)
            {

                throw new Exception("System Error!", exception);
            }
        }

        internal Student GetStudentPersonalInformationFor(string regNoOrEmail)
        {
            try
            {
                Connection.Open();
                string selectQuery = string.Format("SELECT s.Id, s.Name, s.Email, s.Contact_No,  s.Address,s.DepartmentId, s.Registation_Date, s.RegNo, d.Code FROM t_StudentRegistation as s LEFT OUTER JOIN t_Department as d ON s.DepartmentId = d.Id where RegNo='"+regNoOrEmail+"' or  Email='"+regNoOrEmail+"' or Contact_No='"+regNoOrEmail+"'");
                Command.CommandText = selectQuery;
                SqlDataReader reader = Command.ExecuteReader();
                while (reader.Read())
                {
                    aStudent.RegNo = reader["RegNo"].ToString();
                    aStudent.Name = reader["Name"].ToString();
                    aStudent.Email = reader["Email"].ToString();
                    aStudent.ContactNumber = reader["Contact_No"].ToString();
                    aStudent.Address = reader["Address"].ToString();
                    aStudent.Department.Code = reader["Code"].ToString();
                    aStudent.Department.DeptId = Convert.ToInt32(reader["DepartmentId"]);
                    aStudent.RegistrationDate = Convert.ToDateTime(reader["Registation_Date"]);

                }
            }
            catch(FormatException exception)
                {
                    
                    throw new Exception("Input Type is not Correct",exception);
                }
            catch (Exception exception)
            {

                throw new Exception("Sorry!System Error!Please Try Again!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return aStudent;

        }
    }
}